package cn.s1995.sloth.bizs.mapper;

import cn.s1995.sloth.bizs.oo.req.ChartREQ;
import cn.s1995.sloth.bizs.oo.vo.ChartVO;
import cn.s1995.sloth.bizs.oo.vo.DashboardOrderStateVO;
import cn.s1995.sloth.bizs.oo.vo.DashboardStatisticsVO;
import cn.s1995.sloth.bizs.oo.vo.RankingShopListVO;
import cn.s1995.sloth.common.data.datascope.SlothBaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * DashboardMapper
 *
 * @author Lex
 * @description
 * @date 2024/04/30 13:57
 */
@Mapper
public interface DashboardMapper extends SlothBaseMapper {

    @Select("""
    <script>
        SELECT
            ( 
                ( SELECT IFNULL(SUM(pay_amount), 0) FROM biz_pay_flow WHERE pay_state = 1 AND DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d') ) 
                    - ( SELECT IFNULL(SUM(pay_amount), 0) FROM biz_pay_flow WHERE pay_state = 1 AND pay_type IN (2, 4) AND DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d') ) 
            ) AS incomeToday,
            ( 
                ( SELECT IFNULL(SUM(pay_amount), 0) FROM biz_pay_flow WHERE pay_state = 1 ) 
                    - ( SELECT IFNULL(SUM(pay_amount), 0) FROM biz_pay_flow WHERE pay_state = 1 AND pay_type IN (2, 4) ) 
            ) AS incomeTotal,

            ( SELECT IFNULL(COUNT(*), 0) FROM biz_order WHERE del_flag = 0 AND DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d') ) AS orderToday,
            ( SELECT IFNULL(COUNT(*), 0) FROM biz_order WHERE del_flag = 0 ) AS orderTotal,

            ( SELECT IFNULL(COUNT(*), 0) FROM app_user WHERE del_flag = 0 AND DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d') ) AS userToday,
            ( SELECT IFNULL(COUNT(*), 0) FROM app_user WHERE del_flag = 0 ) AS userTotal,

            ( SELECT IFNULL(COUNT(*), 0) FROM biz_prod WHERE del_flag = 0 AND DATE_FORMAT( create_time, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d') ) AS prodToday,
            ( SELECT IFNULL(COUNT(*), 0) FROM biz_prod WHERE del_flag = 0 ) AS prodTotal
        FROM DUAL
    </script>
    """)
    @ResultType(DashboardStatisticsVO.class)
    DashboardStatisticsVO getStatistics();

    @Select("""
    <script>
        SELECT
            IFNULL(SUM(CASE WHEN state = 1 THEN 1 ELSE 0 END), 0) AS unpay,
            IFNULL(SUM(CASE WHEN state = 2 THEN 1 ELSE 0 END), 0) AS paid,
            IFNULL(SUM(CASE WHEN state = 3 THEN 1 ELSE 0 END), 0) AS consignment,
            IFNULL(SUM(CASE WHEN state = 4 THEN 1 ELSE 0 END), 0) AS confirm,
            IFNULL(SUM(CASE WHEN state = 5 THEN 1 ELSE 0 END), 0) AS completed,
            IFNULL(SUM(CASE WHEN state = 6 THEN 1 ELSE 0 END), 0) AS cancel,
            IFNULL(SUM(CASE WHEN 1 = 1 THEN 1 ELSE 0 END), 0) AS total
        FROM biz_order
        WHERE del_flag = 0
            <if test="req.rangeTime != null and req.rangeTime.length == 2">
                AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
            </if>  
    </script>
    """)
    @ResultType(DashboardOrderStateVO.class)
    DashboardOrderStateVO getStateOrders(@Param("req") ChartREQ req);


    @Select("""
    <script>
        SELECT
            t.*
        FROM (
            SELECT
                s.id,
                s.`name`,
                s.avatar,
                s.intro,
                s.address,
                IFNULL(SUM(bp.sale_num), 0) AS saleNum
            FROM biz_shop s
            LEFT JOIN biz_prod bp ON bp.shop_id = s.id AND bp.del_flag = 0
            WHERE s.del_flag = 0
            GROUP BY s.id
        ) t
        ORDER BY t.saleNum DESC
        LIMIT #{limit}
    </script>
    """)
    @ResultType(RankingShopListVO.class)
    List<RankingShopListVO> getRankingShopList(@Param("limit") Long limit);

    @Select("""
    <script>
        SELECT
            DATE_FORMAT( create_time, #{req.sqlGroup} ) AS label,
            IFNULL( SUM( pay_amount ), 0 ) AS `value`
        FROM biz_pay_flow
        WHERE 1 = 1
            AND pay_type IN ( 1, 3 )
            AND pay_state = 1
        <if test="req.rangeTime != null and req.rangeTime.length == 2">
            AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
        </if>
        GROUP BY label
        ORDER BY label
    </script>
    """)
    @ResultType(ChartVO.class)
    List<ChartVO> getChartForIncome(@Param("req") ChartREQ req);

    @Select("""
    <script>
        SELECT
            DATE_FORMAT( create_time, #{req.sqlGroup} ) AS label,
            IFNULL( COUNT( * ), 0 ) AS `value`
        FROM biz_order
        WHERE 1 = 1
            AND del_flag = 0
            AND state BETWEEN 1 AND 5
        <if test="req.rangeTime != null and req.rangeTime.length == 2">
            AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
        </if>
       GROUP BY label
       ORDER BY label
    </script>
    """)
    @ResultType(ChartVO.class)
    List<ChartVO> getChartForOrder(@Param("req") ChartREQ req);

    @Select("""
    <script>
        SELECT
            DATE_FORMAT( create_time, #{req.sqlGroup} ) AS label,
            IFNULL( COUNT( * ), 0 ) AS `value`
        FROM biz_order_refund
        WHERE 1 = 1
            AND process_state = 1
         <if test="req.rangeTime != null and req.rangeTime.length == 2">
            AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
         </if>
        GROUP BY label
        ORDER BY label
    </script>
    """)
    @ResultType(ChartVO.class)
    List<ChartVO> getChartForOrderRefund(@Param("req") ChartREQ req);

    @Select("""
    <script>
        SELECT
            DATE_FORMAT( create_time, #{req.sqlGroup} ) AS label,
            COUNT( * ) AS `value`
        FROM biz_order
        WHERE 1 = 1
         AND del_flag = 0
         AND state BETWEEN 1 AND 5
        <if test="req.rangeTime != null and req.rangeTime.length == 2">
            AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
        </if>
        GROUP BY label
        ORDER BY label
    </script>
    """)
    @ResultType(ChartVO.class)
    List<ChartVO> getChartForUserGrowth(@Param("req") ChartREQ req);

    @Select("""
    <script>
        SELECT
            DATE_FORMAT( create_time, #{req.sqlGroup} ) AS label,
            COUNT( * ) AS `value`
        FROM biz_user_visitor_record
        WHERE 1 = 1
        <if test="req.rangeTime != null and req.rangeTime.length == 2">
            AND create_time BETWEEN #{req.rangeTime[0]} AND #{req.rangeTime[1]}
        </if>
        GROUP BY label
        ORDER BY label
    </script>
    """)
    @ResultType(ChartVO.class)
    List<ChartVO> getChartForUserVisitor(@Param("req") ChartREQ req);

}
